using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BaHuan.Common;
using BaHuan.Entities;
/*
 * For each DB unit, normally tables, we create a DAO class for it
 * this will enalbe us to do some basic tasks for a DB unit
 * such as Save, Delete, GetAll, GetById....
 * 
 * */
namespace BaHuan.DataLayer
{
	
	class PhieuXuatHangDAO:BaseDAO
	{
	
		// save into DB, @id = -1: insert else, update
		public ResultObj SavePhieuXuatHang(PhieuXuatHang phieuxuathang)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			SqlConnection conObj = DataConnection.GetSqlConnection();
			SqlCommand cmd = CreateSqlCommand("spAddPhieuXuatHang", CommandType.StoredProcedure);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "spAddPhieuXuatHang";
			SqlParameter param = new SqlParameter();
			param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, phieuxuathang.Id);
			cmd.Parameters.Add(param);
            param = CreateParameter("@Prefix", DbType.String, ParameterDirection.Input, phieuxuathang.Prefix);
            cmd.Parameters.Add(param);
            param = CreateParameter("@OrderNumber", DbType.String, ParameterDirection.Input, phieuxuathang.OrderNumber);
            cmd.Parameters.Add(param);
            param = CreateParameter("@MaSo", DbType.String, ParameterDirection.Input, phieuxuathang.MaSo);
            cmd.Parameters.Add(param);
            param = CreateParameter("@History", DbType.String, ParameterDirection.Input, phieuxuathang.History);
            cmd.Parameters.Add(param);
			param = CreateParameter("@NgayThang", DbType.DateTime, ParameterDirection.Input, phieuxuathang.NgayThang);
			cmd.Parameters.Add(param);
			param = CreateParameter("@KhachHang", DbType.String, ParameterDirection.Input, phieuxuathang.KhachHang);
			cmd.Parameters.Add(param);
			param = CreateParameter("@ChiNhanhKhachHang", DbType.String, ParameterDirection.Input, phieuxuathang.ChiNhanhKhachHang);
			cmd.Parameters.Add(param);
			param = CreateParameter("@DiaChi", DbType.String, ParameterDirection.Input, phieuxuathang.DiaChi);
			cmd.Parameters.Add(param);
			param = CreateParameter("@IdChiNhanhCongTy", DbType.Int32, ParameterDirection.Input, phieuxuathang.IdChiNhanhCongTy);
			cmd.Parameters.Add(param);
			param = CreateParameter("@MaSoThue", DbType.String, ParameterDirection.Input, phieuxuathang.MaSoThue);
			cmd.Parameters.Add(param);
			param = CreateParameter("@HinhThucThanhToan", DbType.String, ParameterDirection.Input, phieuxuathang.HinhThucThanhToan);
			cmd.Parameters.Add(param);
			param = CreateParameter("@NguoiLap", DbType.String, ParameterDirection.Input, phieuxuathang.NguoiLap);
			cmd.Parameters.Add(param);
			param = CreateParameter("@TongTienHang", DbType.Int64, ParameterDirection.Input, phieuxuathang.TongTienHang);
			cmd.Parameters.Add(param);
			param = CreateParameter("@VAT", DbType.Int64, ParameterDirection.Input, phieuxuathang.VAT);
			cmd.Parameters.Add(param);
			param = CreateParameter("@TienVAT", DbType.Int64, ParameterDirection.Input, phieuxuathang.TienVAT);
			cmd.Parameters.Add(param);
			param = CreateParameter("@ChietKhau", DbType.Int64, ParameterDirection.Input, phieuxuathang.ChietKhau);
			cmd.Parameters.Add(param);
			param = CreateParameter("@TienChietKhau", DbType.Int64, ParameterDirection.Input, phieuxuathang.TienChietKhau);
			cmd.Parameters.Add(param);
			param = CreateParameter("@TongCong", DbType.Int64, ParameterDirection.Input, phieuxuathang.TongCong);
			cmd.Parameters.Add(param);
            //param = CreateParameter("@NguoiKiemTraHang", DbType.String, ParameterDirection.Input, phieuxuathang.NguoiKiemTraHang);
            //cmd.Parameters.Add(param);
            param = CreateParameter("@NgayKiemTraHang", DbType.DateTime, ParameterDirection.Input, phieuxuathang.NgayKiemTraHang);
            cmd.Parameters.Add(param);
            param = CreateParameter("@NgayChungTu", DbType.DateTime, ParameterDirection.Input, phieuxuathang.NgayChungTu);
            cmd.Parameters.Add(param);
            //param = CreateParameter("@XeChoHang", DbType.String, ParameterDirection.Input, phieuxuathang.XeChoHang);
            //cmd.Parameters.Add(param);
            //param = CreateParameter("@KeToanTruong", DbType.String, ParameterDirection.Input, phieuxuathang.KeToanTruong);
            //cmd.Parameters.Add(param);
			param = CreateParameter("@SoHoaDonDo", DbType.String, ParameterDirection.Input, phieuxuathang.SoHoaDonDo);
			cmd.Parameters.Add(param);
            param = CreateParameter("@IsDelete", DbType.Int32, ParameterDirection.Input, phieuxuathang.IsDelete);
            cmd.Parameters.Add(param);
			param = CreateParameter("@ReturnVal", DbType.String, ParameterDirection.ReturnValue, result.ReturnCode);
			cmd.Parameters.Add(param);
			conObj.Open();
			cmd.ExecuteNonQuery();
			conObj.Close();
			result.ReturnCode = Convert.ToInt32(cmd.Parameters["@ReturnVal"].Value);
			
			return result;
		}

        public ResultObj UpdatePhieuXuatHang(PhieuXuatHang phieuxuathang)
        {
            ResultObj result = new ResultObj();
            result.Result = null;
            SqlConnection conObj = DataConnection.GetSqlConnection();
            SqlCommand cmd = CreateSqlCommand("spAddPhieuXuatHang", CommandType.StoredProcedure);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "spAddPhieuXuatHang";
            SqlParameter param = new SqlParameter();
            param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, phieuxuathang.Id);
            cmd.Parameters.Add(param);
            param = CreateParameter("@Prefix", DbType.String, ParameterDirection.Input, phieuxuathang.Prefix);
            cmd.Parameters.Add(param);
            param = CreateParameter("@OrderNumber", DbType.String, ParameterDirection.Input, phieuxuathang.OrderNumber);
            cmd.Parameters.Add(param);
            param = CreateParameter("@MaSo", DbType.String, ParameterDirection.Input, phieuxuathang.MaSo);
            cmd.Parameters.Add(param);
            param = CreateParameter("@History", DbType.String, ParameterDirection.Input, phieuxuathang.History);
            cmd.Parameters.Add(param);
            param = CreateParameter("@NgayThang", DbType.DateTime, ParameterDirection.Input, phieuxuathang.NgayThang);
            cmd.Parameters.Add(param);
            param = CreateParameter("@KhachHang", DbType.String, ParameterDirection.Input, phieuxuathang.KhachHang);
            cmd.Parameters.Add(param);
            param = CreateParameter("@ChiNhanhKhachHang", DbType.String, ParameterDirection.Input, phieuxuathang.ChiNhanhKhachHang);
            cmd.Parameters.Add(param);
            param = CreateParameter("@DiaChi", DbType.String, ParameterDirection.Input, phieuxuathang.DiaChi);
            cmd.Parameters.Add(param);
            param = CreateParameter("@IdChiNhanhCongTy", DbType.Int32, ParameterDirection.Input, phieuxuathang.IdChiNhanhCongTy);
            cmd.Parameters.Add(param);
            param = CreateParameter("@MaSoThue", DbType.String, ParameterDirection.Input, phieuxuathang.MaSoThue);
            cmd.Parameters.Add(param);
            param = CreateParameter("@HinhThucThanhToan", DbType.String, ParameterDirection.Input, phieuxuathang.HinhThucThanhToan);
            cmd.Parameters.Add(param);
            param = CreateParameter("@NguoiLap", DbType.String, ParameterDirection.Input, phieuxuathang.NguoiLap);
            cmd.Parameters.Add(param);
            param = CreateParameter("@TongTienHang", DbType.Int64, ParameterDirection.Input, phieuxuathang.TongTienHang);
            cmd.Parameters.Add(param);
            param = CreateParameter("@VAT", DbType.Int64, ParameterDirection.Input, phieuxuathang.VAT);
            cmd.Parameters.Add(param);
            param = CreateParameter("@TienVAT", DbType.Int64, ParameterDirection.Input, phieuxuathang.TienVAT);
            cmd.Parameters.Add(param);
            param = CreateParameter("@ChietKhau", DbType.Int64, ParameterDirection.Input, phieuxuathang.ChietKhau);
            cmd.Parameters.Add(param);
            param = CreateParameter("@TienChietKhau", DbType.Int64, ParameterDirection.Input, phieuxuathang.TienChietKhau);
            cmd.Parameters.Add(param);
            param = CreateParameter("@TongCong", DbType.Int64, ParameterDirection.Input, phieuxuathang.TongCong);
            cmd.Parameters.Add(param);
            param = CreateParameter("@NguoiKiemTraHang", DbType.String, ParameterDirection.Input, phieuxuathang.NguoiKiemTraHang);
            cmd.Parameters.Add(param);
            param = CreateParameter("@NgayKiemTraHang", DbType.DateTime, ParameterDirection.Input, phieuxuathang.NgayKiemTraHang);
            cmd.Parameters.Add(param);
            param = CreateParameter("@XeChoHang", DbType.String, ParameterDirection.Input, phieuxuathang.XeChoHang);
            cmd.Parameters.Add(param);
            param = CreateParameter("@KeToanTruong", DbType.String, ParameterDirection.Input, phieuxuathang.KeToanTruong);
            cmd.Parameters.Add(param);
            param = CreateParameter("@SoHoaDonDo", DbType.String, ParameterDirection.Input, phieuxuathang.SoHoaDonDo);
            cmd.Parameters.Add(param);
            param = CreateParameter("@NgayChungTu", DbType.DateTime, ParameterDirection.Input, phieuxuathang.NgayChungTu);
            cmd.Parameters.Add(param);
            param = CreateParameter("@IsDelete", DbType.Int32, ParameterDirection.Input, phieuxuathang.IsDelete);
            cmd.Parameters.Add(param);
            param = CreateParameter("@ReturnVal", DbType.String, ParameterDirection.ReturnValue, result.ReturnCode);
            cmd.Parameters.Add(param);
            conObj.Open();
            cmd.ExecuteNonQuery();
            conObj.Close();
            result.ReturnCode = Convert.ToInt32(cmd.Parameters["@ReturnVal"].Value);

            return result;
        }

		// Get all PhieuXuatHang in DB with IsDelete = 0
		public ResultObj GetAllPhieuXuatHang( )
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			List<PhieuXuatHang> lst = null;
			SqlCommand cmd = CreateSqlCommand("spGetAllPhieuXuatHang", CommandType.StoredProcedure);
			cmd.Connection.Open();
			using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
			lst = new List<PhieuXuatHang>();
			PhieuXuatHang item = null;
			// reader all data from DB one by one
			while (reader.Read())
			{
			item = new PhieuXuatHang();
			item.Id = Convert.ToInt32(reader["Id"]);
            item.Prefix = Convert.ToString(reader["Prefix"]);
            item.OrderNumber = Convert.ToString(reader["OrderNumber"]);
            item.MaSo = Convert.ToString(reader["MaSo"]);
            item.History = Convert.ToString(reader["History"]);
			item.NgayThang = Convert.ToDateTime(reader["NgayThang"]);
			item.KhachHang = Convert.ToString(reader["KhachHang"]);
			item.ChiNhanhKhachHang = Convert.ToString(reader["ChiNhanhKhachHang"]);
			item.DiaChi = Convert.ToString(reader["DiaChi"]);
			item.IdChiNhanhCongTy = Convert.ToInt32(reader["IdChiNhanhCongTy"]);
			item.MaSoThue = Convert.ToString(reader["MaSoThue"]);
			item.HinhThucThanhToan = Convert.ToString(reader["HinhThucThanhToan"]);
			item.NguoiLap = Convert.ToString(reader["NguoiLap"]);
			item.TongTienHang = Convert.ToInt64(reader["TongTienHang"]);
			item.VAT = Convert.ToInt64(reader["VAT"]);
			item.TienVAT = Convert.ToInt64(reader["TienVAT"]);
			item.ChietKhau = Convert.ToInt64(reader["ChietKhau"]);
			item.TienChietKhau = Convert.ToInt64(reader["TienChietKhau"]);
			item.TongCong = Convert.ToInt64(reader["TongCong"]);
			item.NguoiKiemTraHang = Convert.ToString(reader["NguoiKiemTraHang"]);
            if (reader["NgayChungTu"] != DBNull.Value)
                item.NgayChungTu = Convert.ToDateTime(reader["NgayChungTu"]);
            if (reader["NgayKiemTraHang"] != DBNull.Value)
                item.NgayKiemTraHang = Convert.ToDateTime(reader["NgayKiemTraHang"]);

			item.XeChoHang = Convert.ToString(reader["XeChoHang"]);
			item.SoHoaDonDo = Convert.ToString(reader["SoHoaDonDo"]);
            item.KeToanTruong = Convert.ToString(reader["KeToanTruong"]);
            item.IsDelete = Convert.ToInt32(reader["IsDelete"]);
			lst.Add(item);
			}
			}
			result.Result = lst;
			result.ReturnCode = 1;
			
			return result;
		}
		
		// Get all PhieuXuatHang in DB with condition
		public ResultObj GetAllPhieuXuatHangByID(PhieuXuatHang phieuxuathang)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			List<PhieuXuatHang> lst = null;
			SqlCommand cmd = CreateSqlCommand("spGetAllPhieuXuatHangByID", CommandType.StoredProcedure);
			SqlParameter param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, phieuxuathang.Id);
			cmd.Parameters.Add(param);
			cmd.Connection.Open();
			using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
			lst = new List<PhieuXuatHang>();
			PhieuXuatHang item = null;
			// reader all data from DB one by one
			while (reader.Read())
			{
			item = new PhieuXuatHang();
			item.Id = Convert.ToInt32(reader["Id"]);
            item.Prefix = Convert.ToString(reader["Prefix"]);
            item.OrderNumber = Convert.ToString(reader["OrderNumber"]);
            item.MaSo = Convert.ToString(reader["MaSo"]);
            item.History = Convert.ToString(reader["History"]);
			item.NgayThang = Convert.ToDateTime(reader["NgayThang"]);
			item.KhachHang = Convert.ToString(reader["KhachHang"]);
			item.ChiNhanhKhachHang = Convert.ToString(reader["ChiNhanhKhachHang"]);
			item.DiaChi = Convert.ToString(reader["DiaChi"]);
			item.IdChiNhanhCongTy = Convert.ToInt32(reader["IdChiNhanhCongTy"]);
			item.MaSoThue = Convert.ToString(reader["MaSoThue"]);
			item.HinhThucThanhToan = Convert.ToString(reader["HinhThucThanhToan"]);
			item.NguoiLap = Convert.ToString(reader["NguoiLap"]);
			item.TongTienHang = Convert.ToInt64(reader["TongTienHang"]);
			item.VAT = Convert.ToInt64(reader["VAT"]);
			item.TienVAT = Convert.ToInt64(reader["TienVAT"]);
			item.ChietKhau = Convert.ToInt64(reader["ChietKhau"]);
			item.TienChietKhau = Convert.ToInt64(reader["TienChietKhau"]);
			item.TongCong = Convert.ToInt64(reader["TongCong"]);
			item.NguoiKiemTraHang = Convert.ToString(reader["NguoiKiemTraHang"]);

            if (reader["NgayKiemTraHang"] != DBNull.Value)
                item.NgayKiemTraHang = Convert.ToDateTime(reader["NgayKiemTraHang"]);

			item.XeChoHang = Convert.ToString(reader["XeChoHang"]);
			item.SoHoaDonDo = Convert.ToString(reader["SoHoaDonDo"]);
            item.KeToanTruong = Convert.ToString(reader["KeToanTruong"]);
            item.IsDelete = Convert.ToInt32(reader["IsDelete"]);
			lst.Add(item);
			}
			}
			result.Result = lst;
			result.ReturnCode = 1;
			
			return result;
		}
		
		// Delete a PhieuXuatHang out of DB with condition
		public ResultObj DeletePhieuXuatHang(PhieuXuatHang phieuxuathang)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			SqlConnection conObj = DataConnection.GetSqlConnection();
			SqlCommand cmd = CreateSqlCommand("spDeletePhieuXuatHang", CommandType.StoredProcedure);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "spDeletePhieuXuatHang";
			SqlParameter param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, phieuxuathang.Id);
			cmd.Parameters.Add(param);
			conObj.Open();
			result.ReturnCode = cmd.ExecuteNonQuery();
			conObj.Close();
			
			return result;
		}
		
		// Get info of CTPhieuXuatHang from DB with condition
		public ResultObj GetCTPhieuXuatHang(int IdCTPhieuXuatHang)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			List<CTPhieuXuatHang> lst = null;
			SqlCommand cmd = CreateSqlCommand("spGetCTPhieuXuatHangByID", CommandType.StoredProcedure);
			SqlParameter param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, IdCTPhieuXuatHang);
			cmd.Parameters.Add(param);
			cmd.Connection.Open();
			using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
			lst = new List<CTPhieuXuatHang>();
			CTPhieuXuatHang item = null;
			// reader all data from DB one by one
			while (reader.Read())
			{
			item = new CTPhieuXuatHang();
			item.Id = Convert.ToInt32(reader["Id"]);
			item.IdPhieuXuatHang = Convert.ToInt32(reader["IdPhieuXuatHang"]);
			item.TenHang = Convert.ToString(reader["TenHang"]);
			item.MaHang = Convert.ToString(reader["MaHang"]);
			item.DonViTinh = Convert.ToString(reader["DonViTinh"]);
			item.DonViTinhQuyDoi = Convert.ToString(reader["DonViTinhQuyDoi"]);
			item.SoLuong = Convert.ToInt64(reader["SoLuong"]);
			item.SoLuongQuyDoi = Convert.ToInt64(reader["SoLuongQuyDoi"]);
			item.DonGia = Convert.ToInt64(reader["DonGia"]);
			item.ThanhTien = Convert.ToInt64(reader["ThanhTien"]);
			lst.Add(item);
			}
			}
			result.Result = lst;
			result.ReturnCode = 1;
			
			return result;
		}

        public ResultObj GetAllPhieuXuatHangLien3()
        {
            ResultObj result = new ResultObj();
            result.Result = null;
            List<PhieuXuatHang> lst = null;
            SqlCommand cmd = CreateSqlCommand("spGetAllPhieuXuatHangLien3", CommandType.StoredProcedure);
            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                lst = new List<PhieuXuatHang>();
                PhieuXuatHang item = null;
                // reader all data from DB one by one
                while (reader.Read())
                {
                    item = new PhieuXuatHang();
                    item.Id = Convert.ToInt32(reader["Id"]);
                    item.Prefix = Convert.ToString(reader["Prefix"]);
                    item.OrderNumber = Convert.ToString(reader["OrderNumber"]);
                    item.MaSo = Convert.ToString(reader["MaSo"]);
                    item.History = Convert.ToString(reader["History"]);
                    item.NgayThang = Convert.ToDateTime(reader["NgayThang"]);
                    item.KhachHang = Convert.ToString(reader["KhachHang"]);
                    item.ChiNhanhKhachHang = Convert.ToString(reader["ChiNhanhKhachHang"]);
                    item.DiaChi = Convert.ToString(reader["DiaChi"]);
                    item.IdChiNhanhCongTy = Convert.ToInt32(reader["IdChiNhanhCongTy"]);
                    item.MaSoThue = Convert.ToString(reader["MaSoThue"]);
                    item.HinhThucThanhToan = Convert.ToString(reader["HinhThucThanhToan"]);
                    item.NguoiLap = Convert.ToString(reader["NguoiLap"]);
                    item.TongTienHang = Convert.ToInt64(reader["TongTienHang"]);
                    item.VAT = Convert.ToInt64(reader["VAT"]);
                    item.TienVAT = Convert.ToInt64(reader["TienVAT"]);
                    item.ChietKhau = Convert.ToInt64(reader["ChietKhau"]);
                    item.TienChietKhau = Convert.ToInt64(reader["TienChietKhau"]);
                    item.TongCong = Convert.ToInt64(reader["TongCong"]);
                    item.NguoiKiemTraHang = Convert.ToString(reader["NguoiKiemTraHang"]);
                    if (reader["NgayKiemTraHang"] != DBNull.Value)
                    item.NgayKiemTraHang = Convert.ToDateTime(reader["NgayKiemTraHang"]);
                    item.XeChoHang = Convert.ToString(reader["XeChoHang"]);
                    item.SoHoaDonDo = Convert.ToString(reader["SoHoaDonDo"]);
                    item.KeToanTruong = Convert.ToString(reader["KeToanTruong"]);
                    if (reader["NgayChungTu"] != DBNull.Value)
                    item.NgayChungTu = Convert.ToDateTime(reader["NgayChungTu"]);
                    item.SoHieuHoaDon = Convert.ToString(reader["SoHieuHoaDon"]);
                    item.IsDelete = Convert.ToInt32(reader["IsDelete"]);
                    item.SoCT = Convert.ToString(reader["SoCT"]);
                    KiemSoatLien3 kiemsoatlien3 = new KiemSoatLien3();

                    if (reader["GhiChu"] != DBNull.Value)
                        kiemsoatlien3.GhiChu= Convert.ToString(reader["GhiChu"]);
                    
                    if (reader["NgayBaoSoat"] != DBNull.Value)
                        kiemsoatlien3.NgayBaoSoat = Convert.ToDateTime(reader["NgayBaoSoat"]);
                    List<KiemSoatLien3> lstKiemSoatLien3 = new List<KiemSoatLien3>();
                    lstKiemSoatLien3.Add(kiemsoatlien3);
                    item.listKiemSoatLien3 = lstKiemSoatLien3;
                    lst.Add(item);
                }
            }
            result.Result = lst;
            result.ReturnCode = 1;

            return result;
        }
	}
}
